package com.ws.shop.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.ws.shop.bean.Page;
import com.ws.shop.dao.MessageDao;
import com.ws.shop.entity.Message;
import com.ws.shop.util.JdbcUtil;

@Repository(value="messageDao")
public class MessageDaoImpl extends BaseDaoImpl implements MessageDao {

	//获取反馈列表
	@SuppressWarnings("unchecked")
	public void getMessages(final Page page,final int state){
		this.execute(
				new ConnectionCallback(){
					public Object doInConnection(Connection conn)
					throws SQLException, DataAccessException {
						Statement stmt = null;
						ResultSet rs = null;
						try{
							String s = "SELECT COUNT(1) FROM jvf_message WHERE state = " + state;
							stmt = conn.createStatement();
							rs = stmt.executeQuery(s);
							long totalnum = 0;
							if (rs.next()) {
								totalnum = rs.getLong(1);
								page.setTotalnum(totalnum);
							}
							JdbcUtil.close(rs,stmt,null);
							if(totalnum>0){
								stmt = conn.createStatement();
								StringBuffer bufer = new StringBuffer();
								bufer.append("SELECT id,uid,NAME,message,reply,ip,title,ADDTIME,state FROM jvf_message WHERE state  =  " + state);
								rs = stmt.executeQuery(bufer.append(" limit ").append(page.getStartrownum()).append(",").append(page.getPagesize()).toString());
								while (rs.next()) {
								    Message message = new Message();
								    message.setId(rs.getInt(1));
								    message.setUid(rs.getInt(2));
								    message.setName(rs.getString(3));
								    message.setMessage(rs.getString(4));
								    message.setReply(rs.getString(5));
								    message.setIp(rs.getString(6));
								    message.setTitle(rs.getString(7));
								    message.setAddtime(rs.getTimestamp(8));
								    message.setState(rs.getInt(9));
									page.addData(message);
								}
							}
						}catch (Exception e) {
							e.printStackTrace();
						} finally {
							JdbcUtil.close(rs, stmt, null);
						}
						return null;
					}
				}
		);
	}
	
	public void getMessageByUid(final Page page,final int state ,final long uid){
		this.execute(
				new ConnectionCallback(){
					@SuppressWarnings("unchecked")
					public Object doInConnection(Connection conn)
					throws SQLException, DataAccessException {
						Statement stmt = null;
						ResultSet rs = null;
						try{
							String s = "SELECT COUNT(1) FROM jvf_message WHERE uid = "+ uid +" and state = " + state;
							stmt = conn.createStatement();
							rs = stmt.executeQuery(s);
							long totalnum = 0;
							if (rs.next()) {
								totalnum = rs.getLong(1);
								page.setTotalnum(totalnum);
							}
							JdbcUtil.close(rs,stmt,null);
							if(totalnum>0){
								stmt = conn.createStatement();
								StringBuffer bufer = new StringBuffer();
								bufer.append("SELECT id,uid,NAME,message,reply,ip,title,ADDTIME,state FROM jvf_message WHERE uid = "+uid+" AND state  =  " + state);
								rs = stmt.executeQuery(bufer.append(" limit ").append(page.getStartrownum()).append(",").append(page.getPagesize()).toString());
								while (rs.next()) {
								    Message message = new Message();
								    message.setId(rs.getInt(1));
								    message.setUid(rs.getInt(2));
								    message.setName(rs.getString(3));
								    message.setMessage(rs.getString(4));
								    message.setReply(rs.getString(5));
								    message.setIp(rs.getString(6));
								    message.setTitle(rs.getString(7));
								    message.setAddtime(rs.getTimestamp(8));
								    message.setState(rs.getInt(9));
									page.addData(message);
								}
							}
						}catch (Exception e) {
							e.printStackTrace();
						} finally {
							JdbcUtil.close(rs, stmt, null);
						}
						return null;
					}
				}
		);
	}

	public int saveMessage(Message message) {
		try{
			String s ="INSERT INTO jvf_message(uid,NAME,message,ip,title,ADDTIME) VALUES(?,?,?,?,?,NOW())";
			return this.update(s, new Object[]{message.getUid(),message.getName(),message.getMessage(),message.getIp(),message.getTitle()});
		}catch(Exception ex){
			ex.printStackTrace();
			return -1;
		}
	}

	final String findMessageListByPage_count = "select count(1) from jvf_message";
	final String findMessageListByPage_list = "select id,uid,name,ip,title,addtime,state from jvf_message";
	final String findMessageListByPage_order = " order by id desc";
	public Page findMessageListByPage(int pageno, int pagesize, String start,
			String end, String name, int aid) {
		StringBuilder query = new StringBuilder(" where 1=1");
		if(name != null){
			query.append(" and name='").append(name).append("'");
		}
		if(aid > 0){
			query.append(" and aid=").append(aid);
		}
		query.append(" and addtime<='").append(end).append(" 23:59:59'")
			.append(" and addtime>='").append(start).append(" 00:00:00'");
		return this.findPage(pageno, pagesize, new StringBuilder(findMessageListByPage_count).append(query).toString(),
				new StringBuilder(findMessageListByPage_list).append(query).append(findMessageListByPage_order));
	}

	final String reply_sql = "update jvf_message set reply=?,state=? where id=?";
	public void reply(Message message) {
		this.update(reply_sql, message.getReply(),message.getState(),message.getId());
	}

	final String findMessageById = "select uid,name,message,reply,ip,title,addtime,state from jvf_message where id=?";
	public Message findMessageById(final int id) {
		return this.query(findMessageById, new ResultSetExtractor<Message>(){
			public Message extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if(!rs.next())
					return null;
				Message m = new Message();
				m.setId(id);
				m.setUid(rs.getInt(1));
				m.setName(rs.getString(2));
				m.setMessage(rs.getString(3));
				m.setReply(rs.getString(4));
				m.setIp(rs.getString(5));
				m.setTitle(rs.getString(6));
				m.setAddtime(rs.getTimestamp(7));
				m.setState(rs.getInt(8));
				return m;
			}
		}, id);
	}
}
